using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using FormsGenerator.FormsGeneratorException;

namespace FormsGenerator.DataAccess
{
    /// <summary>
    /// Summary description for DBHandler.
    /// </summary>
    internal class DBHandler : IDBHandler
    {
        #region IDBHandler Members

        public void SaveForm(string strGUID, string formName, XmlDocument formXmlDoc, string strUserId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error saving form! Either the form does not exists or you do not have permission to save the form.");
            }
            if (dt.Rows[0]["LockedBy"].Equals(DBNull.Value) || ((string) dt.Rows[0]["LockedBy"]).Trim().Equals(""))
            {
                throw new ApplicationException("Cannot save form. Form is not locked by you!");
            }
                //User can only save the form which was locked by the user.
            if (((string) dt.Rows[0]["LockedBy"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                var sqlCommand =
                    new SqlCommand(
                        @"UPDATE tblForms SET Name = @Name, XML = @XML WHERE (guid = @guid) AND LockedBy like @UserId AND (Owner like @UserId OR 
														guid IN (Select guid from tblFormsAccess WHERE UserId = @UserId OR UserId = 'all'))",
                        sqlConnection);
                sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
                sqlCommand.Parameters.Add(new SqlParameter("@XML", SqlDbType.NText));
                sqlCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.NVarChar));

                sqlCommand.Parameters["@Guid"].Value = newGuid;
                sqlCommand.Parameters["@Name"].Value = formName;
                sqlCommand.Parameters["@XML"].Value = formXmlDoc.InnerXml;
                sqlCommand.Parameters["@UserId"].Value = strUserId;

                sqlConnection.Open();
                int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                if (numOfRowsUpdated != 1)
                {
                    throw new ApplicationException("There was an error updating the Form!");
                }
            }
            else
            {
                throw new PermissionDeniedException("The form is locked by user " + (string) dt.Rows[0]["LockedBy"] +
                                                    ", since " + dt.Rows[0]["LockedSince"] +
                                                    ", and you cannot save a form which is not locked by you!");
            }
        }

        public DataTable CreateForm(string formName, XmlDocument formXmlDoc, string owner)
        {
            Guid guid = Guid.NewGuid();

            var sqlDataAdapter = new SqlDataAdapter();
            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            var sqlCommand = new SqlCommand();

            sqlCommand.CommandText =
                "INSERT INTO tblForms(Guid, Name, CreatedDate, Owner, XML, LockedBy, LockedSince) " +
                "VALUES (@Guid, @Name, GETDATE(), @Owner, @XML, @LockedBy, GETDATE()); " +
                "SELECT * from tblForms Where Guid = @Guid";

            sqlCommand.Parameters.Add(new SqlParameter("@Guid", SqlDbType.UniqueIdentifier));
            sqlCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
            sqlCommand.Parameters.Add(new SqlParameter("@Owner", SqlDbType.NVarChar));
            sqlCommand.Parameters.Add(new SqlParameter("@XML", SqlDbType.NText));
            sqlCommand.Parameters.Add(new SqlParameter("@LockedBy", SqlDbType.NVarChar));

            sqlCommand.Parameters["@Guid"].Value = guid;
            sqlCommand.Parameters["@Name"].Value = formName;
            sqlCommand.Parameters["@Owner"].Value = owner;
            sqlCommand.Parameters["@XML"].Value = formXmlDoc.InnerXml;
            sqlCommand.Parameters["@LockedBy"].Value = owner;

            sqlCommand.Connection = sqlConnection;
            sqlDataAdapter.SelectCommand = sqlCommand;
            var dt = new DataTable();
            sqlDataAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetForm(string strGUID)
        {
            var sqlDataAdapter = new SqlDataAdapter();
            var sqlSelectCommand = new SqlCommand();
            var dt = new DataTable();
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            sqlDataAdapter.SelectCommand = sqlSelectCommand;

            sqlSelectCommand.CommandText = "SELECT * FROM tblForms WHERE guid = @Guid";
            sqlSelectCommand.Parameters.Add("@Guid", SqlDbType.UniqueIdentifier);
            sqlSelectCommand.Connection = sqlConnection;

            sqlSelectCommand.Parameters["@Guid"].Value = newGuid;
            sqlDataAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetForms(string strUserId)
        {
            var sqlDataAdapter = new SqlDataAdapter();
            var sqlSelectCommand = new SqlCommand();
            var dt = new DataTable("Form");

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            sqlDataAdapter.SelectCommand = sqlSelectCommand;

            sqlSelectCommand.CommandText =
                @"SELECT * FROM tblForms WHERE Owner like @UserId OR 
											guid IN (Select guid from tblFormsAccess WHERE UserId = @UserId OR UserId = 'all')";
            sqlSelectCommand.Parameters.Add("@UserId", SqlDbType.VarChar);
            sqlSelectCommand.Connection = sqlConnection;

            sqlSelectCommand.Parameters["@UserId"].Value = strUserId;
            sqlDataAdapter.Fill(dt);
            return dt;
        }

        public void LockForm(string strGUID, string strUserId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error locking form! Either the form does not exists or you do not have permission to lock the form.");
            }
            if (dt.Rows[0]["LockedBy"].Equals(DBNull.Value) || ((string) dt.Rows[0]["LockedBy"]).Trim().Equals(""))
            {
                var sqlCommand =
                    new SqlCommand(
                        @"UPDATE tblForms SET LockedBy = @UserId , LockedSince = GETDATE() WHERE (guid = @guid) AND (Owner like @UserId OR 
											guid IN (Select guid from tblFormsAccess WHERE UserId = @UserId OR UserId = 'all'))",
                        sqlConnection);
                sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.NVarChar));

                sqlCommand.Parameters["@Guid"].Value = newGuid;
                sqlCommand.Parameters["@UserId"].Value = strUserId;

                sqlConnection.Open();
                int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                if (numOfRowsUpdated != 1)
                {
                    throw new ApplicationException(
                        "There was an error locking the Form! Either you do not have permission to lock the form or there was a database error");
                }
            }
            else
            {
                throw new PermissionDeniedException("The form is locked by user " + (string) dt.Rows[0]["LockedBy"] +
                                                    ", since " + dt.Rows[0]["LockedSince"]);
            }
        }

        public void UnLockForm(string strGUID, string strUserId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error Unlocking form! Either the form does not exists or you do not have permission to unlock the form.");
            }
            if (dt.Rows[0]["LockedBy"].Equals(DBNull.Value) || ((string) dt.Rows[0]["LockedBy"]).Trim().Equals(""))
            {
                throw new ApplicationException("Form is not locked!");
            }
                //User can only unlock the form which was locked by the user.
            if (((string) dt.Rows[0]["LockedBy"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                var sqlCommand =
                    new SqlCommand(
                        @"UPDATE tblForms SET LockedBy = '', LockedSince = null WHERE (guid = @guid) AND (Owner like @UserId OR 
											guid IN (Select guid from tblFormsAccess WHERE UserId = @UserId OR UserId = 'all'))",
                        sqlConnection);
                sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlCommand.Parameters.Add(new SqlParameter("@UserId", SqlDbType.NVarChar));
                sqlCommand.Parameters["@Guid"].Value = newGuid;
                sqlCommand.Parameters["@UserId"].Value = strUserId;

                sqlConnection.Open();
                int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
                sqlConnection.Close();
                if (numOfRowsUpdated != 1)
                {
                    throw new ApplicationException("There was an error unlocking the Form!");
                }
            }
            else
            {
                throw new PermissionDeniedException("The form is locked by user " + (string) dt.Rows[0]["LockedBy"] +
                                                    ", since " + dt.Rows[0]["LockedSince"] +
                                                    ", and you cannot unlock a form which is not locked by you!");
            }
        }


        public void DeleteForm(string strGUID, string strUserId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error Deleting form! Either the form does not exists or you do not have permission to delete the form.");
            }
            if (dt.Rows[0]["LockedBy"].Equals(DBNull.Value) || ((string) dt.Rows[0]["LockedBy"]).Trim().Equals(""))
            {
                throw new ApplicationException("Form is not locked!");
            }
            if (!((string) dt.Rows[0]["Owner"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                throw new ApplicationException("You cannot delete a form that is not owned by you!");
            }
                //User can only delete the form which was locked by the user.
            if (((string) dt.Rows[0]["LockedBy"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                SqlTransaction deleteTransaction;
                var sqlDeleteAccessCommand = new SqlCommand(@"DELETE from tblFormsAccess WHERE guid = @Guid",
                                                            sqlConnection);
                sqlDeleteAccessCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlDeleteAccessCommand.Parameters["@Guid"].Value = newGuid;

                var sqlDeleteCommand = new SqlCommand(@"DELETE from tblForms WHERE guid = @Guid", sqlConnection);
                sqlDeleteCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlDeleteCommand.Parameters["@Guid"].Value = newGuid;

                sqlConnection.Open();
                deleteTransaction = sqlConnection.BeginTransaction("DeleteFormTransaction");
                sqlDeleteAccessCommand.Transaction = deleteTransaction;
                sqlDeleteCommand.Transaction = deleteTransaction;
                try
                {
                    sqlDeleteAccessCommand.ExecuteNonQuery();
                    int numOfRowsUpdated = sqlDeleteCommand.ExecuteNonQuery();
                    if (numOfRowsUpdated != 1)
                    {
                        throw new ApplicationException("There was an error unlocking the Form!");
                    }
                    deleteTransaction.Commit();
                }
                catch (Exception)
                {
                    deleteTransaction.Rollback("DeleteFormTransaction");
                    throw;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            else
            {
                throw new PermissionDeniedException("The form is locked by user " + (string) dt.Rows[0]["LockedBy"] +
                                                    ", since " + dt.Rows[0]["LockedSince"] +
                                                    ", and you cannot delete a form which is not locked by you!");
            }
        }

        public DataTable GetFormStatus(Guid guid, string strUserId)
        {
            var sqlDataAdapter = new SqlDataAdapter();
            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            var sqlSelectCommand =
                new SqlCommand(
                    @"Select LockedBy, LockedSince, Owner From tblForms WHERE (guid = @guid) AND (Owner like @UserId OR 
											guid IN (Select guid from tblFormsAccess WHERE UserId = @UserId OR UserId = 'all'))");
            sqlDataAdapter.SelectCommand = sqlSelectCommand;
            sqlSelectCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlSelectCommand.Parameters.Add("@UserId", SqlDbType.VarChar);
            sqlSelectCommand.Connection = sqlConnection;

            sqlSelectCommand.Parameters["@guid"].Value = guid;
            sqlSelectCommand.Parameters["@UserId"].Value = strUserId;
            var dt = new DataTable();
            sqlDataAdapter.Fill(dt);
            return dt;
        }

        public DataTable GetFormAccess(string strGUID, string strUserId)
        {
            Guid newGuid = new Guid(strGUID);
            var sqlDataAdapter = new SqlDataAdapter();
            var sqlSelectCommand = new SqlCommand();
            var dt = new DataTable();

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            sqlDataAdapter.SelectCommand = sqlSelectCommand;

            sqlSelectCommand.CommandText =
                @"SELECT * FROM tblFormsAccess, tblForms WHERE tblForms.Owner like @UserId AND 
											tblForms.guid = @guid AND tblFormsAccess.guid = tblForms.guid";
            sqlSelectCommand.Parameters.Add("@UserId", SqlDbType.VarChar);
            sqlSelectCommand.Parameters.Add("@guid", SqlDbType.UniqueIdentifier);
            sqlSelectCommand.Connection = sqlConnection;

            sqlSelectCommand.Parameters["@UserId"].Value = strUserId;
            sqlSelectCommand.Parameters["@guid"].Value = newGuid;
            sqlDataAdapter.Fill(dt);
            return dt;
        }

        public void RemoveFormAccess(string strGUID, string strUserId, string strUserToBeRemovedId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error removing user! Either the form does not exists or you do not have permission to edit form access.");
            }
            if (!((string) dt.Rows[0]["Owner"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                throw new ApplicationException("You cannot edit access to a form that is not owned by you!");
            }
                //User can only remove form access which the user is the owner of.
            sqlConnection.Open();
            SqlTransaction updateTransaction = sqlConnection.BeginTransaction();
            var sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
            sqlCommand.Transaction = updateTransaction;

            try
            {
                sqlCommand.CommandText =
                    @"DELETE from tblFormsAccess WHERE guid = @guid AND UserId = @UserToBeRemovedId";
                sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                sqlCommand.Parameters.Add(new SqlParameter("@UserToBeRemovedId", SqlDbType.NVarChar));

                sqlCommand.Parameters["@Guid"].Value = newGuid;
                sqlCommand.Parameters["@UserToBeRemovedId"].Value = strUserToBeRemovedId;
                int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
                if (numOfRowsUpdated < 1)
                {
                    throw new ApplicationException(
                        "There was an error removing the user! Either you do not have permission to edit the forms access or there was a database error");
                }
                    //Check in form if the user who is beeing removed has locked the form
                if (((string) dt.Rows[0]["LockedBy"]).Trim().ToLower().Equals(strUserToBeRemovedId.ToLower()))
                {
                    sqlCommand =
                        new SqlCommand(
                            @"UPDATE tblForms SET LockedBy = '', LockedSince = null WHERE (guid = @guid)",
                            sqlConnection);
                    sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
                    sqlCommand.Parameters["@Guid"].Value = newGuid;
                    sqlCommand.ExecuteNonQuery();
                }
                updateTransaction.Commit();
            }
            catch (Exception ex)
            {
                try
                {
                    updateTransaction.Rollback();
                }
                catch (SqlException sex)
                {
                    if (updateTransaction.Connection != null)
                    {
                        throw new ApplicationException("An exception of type " + sex.GetType() +
                                                       " with errormessage \"" + ex.Message +
                                                       "\" was encountered while attempting to roll back the transaction.");
                    }
                }

                throw new ApplicationException("An exception of type " + ex.GetType() +
                                               " with errormessage \"" + ex.Message +
                                               "\" was encountered removing user. Please try again or contact a system administrator");
            }
            finally
            {
                sqlConnection.Close();
            }
        }

        public void UpdateFormAccess(string strGUID, string strUserId, string strUserToUpdateId, string strFormsAccessId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error updating user! Either the form does not exists or you do not have permission to edit form access.");
            }
            if (!((string) dt.Rows[0]["Owner"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                throw new ApplicationException("You cannot edit access to a form that is not owned by you!");
            }
                //User can only remove form access which the user is the owner of.
            var sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            sqlConnection.Open();
            sqlCommand.CommandText =
                @"UPDATE tblFormsAccess set UserId = @UserToUpdateId WHERE guid = @guid AND FormsAccessId = @FormsAccessId";
            sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
            sqlCommand.Parameters.Add(new SqlParameter("@UserToUpdateId", SqlDbType.NVarChar));
            sqlCommand.Parameters.Add(new SqlParameter("@FormsAccessId", SqlDbType.Int));

            sqlCommand.Parameters["@FormsAccessId"].Value = strFormsAccessId;
            sqlCommand.Parameters["@Guid"].Value = newGuid;
            sqlCommand.Parameters["@UserToUpdateId"].Value = strUserToUpdateId;
            int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            if (numOfRowsUpdated != 1)
            {
                throw new ApplicationException(
                    "There was an error updating form access! Either you do not have permission to edit the forms access or there was a database error");
            }
        }

        public void InsertFormAccess(string strGUID, string strUserId, string strUserToAddId)
        {
            Guid newGuid = new Guid(strGUID);

            var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["FormsGeneratorConnectionString"].ConnectionString);
            
            DataTable dt = GetFormStatus(newGuid, strUserId);

            if (dt.Rows.Count == 0)
            {
                throw new ApplicationException(
                    "Error adding user! Either the form does not exists or you do not have permission to edit form access.");
            }
            if (!((string) dt.Rows[0]["Owner"]).Trim().ToLower().Equals(strUserId.ToLower()))
            {
                throw new ApplicationException("You cannot edit access to a form that is not owned by you!");
            }
                //User can only remove form access which the user is the owner of.
            var sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;

            sqlConnection.Open();
            sqlCommand.CommandText =
                @"Insert INTO tblFormsAccess (UserId, guid)
										   VALUES  (@UserToAddId, @guid)";

            sqlCommand.Parameters.Add(new SqlParameter("@guid", SqlDbType.UniqueIdentifier));
            sqlCommand.Parameters.Add(new SqlParameter("@UserToAddId", SqlDbType.NVarChar));

            sqlCommand.Parameters["@Guid"].Value = newGuid;
            sqlCommand.Parameters["@UserToAddId"].Value = strUserToAddId;
            int numOfRowsUpdated = sqlCommand.ExecuteNonQuery();
            sqlConnection.Close();
            if (numOfRowsUpdated != 1)
            {
                throw new ApplicationException(
                    "There was an error updating form access! Either you do not have permission to edit the forms access or there was a database error");
            }
        }

        #endregion
    }
}